package renthouse.dao;/*
 * @创建者：黄苟苟
 * @创建时间：2021/5/18   18:10
 * @Description描述
 **/

import renthouse.entry.User;
import renthouse.util.ConnDb;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class UserDao {
    
    public boolean insert(User user) {
        String s="insert into user(name,sex,phone,numberid,loginname,password) values (?,?,?,?,?,?)";
        Connection connection= ConnDb.getConnection();
        PreparedStatement p=null;
        try {
            p=connection.prepareStatement(s);
            p.setString(1, user.getName());
            p.setString(2,user.getSex());
            p.setString(3,user.getPhone());
            p.setString(4,user.getNumberid());
            p.setString(5,user.getLoginname());
            p.setString(6,user.getPassword());

            int i=  p.executeUpdate();
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,p,null);  //关闭
        }

        return false;
    }

   
    public boolean deleteById(int i) {
        String deleteStr="delete from user where userid ="+i;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(deleteStr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;
    }

   
    public boolean updateById(User user) {
        if (user.getId()==0){
            return false;
        }
        //拼接sql语句
        String updateStr="update user set id="+user.getId();

        if (user.getName()!=null){
            updateStr+=",name='"+user.getName()+"'";
        }

        if (user.getSex()!=null){
            updateStr+=",sex='"+user.getSex()+"'";
        }

        if (user.getPhone()!=null){
            updateStr+=",phone='"+user.getPhone()+"'";
        }

        if (user.getNumberid()!=null){
            updateStr+=",numberid='"+user.getNumberid()+"'";
        }

        if (user.getLoginname()!=null){
            updateStr+=",loginname="+user.getLoginname();
        }

        if (user.getPassword()!=null){
            updateStr+=",password="+user.getPassword();
        }


        //加上条件

        updateStr+=" where id="+user.getId();


        Connection connection= ConnDb.getConnection();
        Statement statement= ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(updateStr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;
    }

    
    public List <User> query(User user) {
        String str="SELECT * FROM user where 1=1 ";
        List<User> list=new ArrayList <>();
        if (user.getName()!=null){
            str+=" and name like concat('%','"+user.getName()+"','%')";
        }

        if (user.getSex()!=null){
            str+=" and sex like concat('%','"+user.getSex()+"','%')";
        }

        if (user.getPhone()!=null){
            str+=" and phone like concat('%','"+user.getPhone()+"','%')";
        }

        if (user.getNumberid()!=null){
            str+=" and numberid like concat('%','"+user.getNumberid()+"','%')";
        }

        if (user.getLoginname()!=null){
            str+=" and loginname like concat('%','"+user.getLoginname()+"','%')";
        }

        if (user.getPassword()!=null){
            str+=" and password like concat('%','"+user.getPassword()+"','%')";
        }


        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                User landlord1=new User();
                landlord1.setId(resultSet.getInt("userid"));
                landlord1.setName(resultSet.getString("name"));
                landlord1.setSex(resultSet.getString("sex"));
                landlord1.setPhone(resultSet.getString("phone"));
                landlord1.setNumberid(resultSet.getString("numberid"));
                landlord1.setLoginname(resultSet.getString("loginname"));
                landlord1.setPassword(resultSet.getString("password"));

                list.add(landlord1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;
    }

    
    public List <User> queryForPage(User user, int pageNum, int pageSize) {

        int start = (pageNum - 1) * pageSize;
        String str="SELECT * FROM user where 1=1 ";
        List<User> list=new ArrayList <>();
        if (user.getName()!=null){
            str+=" and name like concat('%','"+user.getName()+"','%')";
        }

        if (user.getSex()!=null){
            str+=" and sex like concat('%','"+user.getSex()+"','%')";
        }

        if (user.getPhone()!=null){
            str+=" and phone like concat('%','"+user.getPhone()+"','%')";
        }

        if (user.getNumberid()!=null){
            str+=" and numberid like concat('%','"+user.getNumberid()+"','%')";
        }

        if (user.getLoginname()!=null){
            str+=" and loginname like concat('%','"+user.getLoginname()+"','%')";
        }

        if (user.getPassword()!=null){
            str+=" and password like concat('%','"+user.getPassword()+"','%')";
        }

        str += " limit " + start + "," + pageSize;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                User landlord1=new User();
                landlord1.setId(resultSet.getInt("userid"));
                landlord1.setName(resultSet.getString("name"));
                landlord1.setSex(resultSet.getString("sex"));
                landlord1.setPhone(resultSet.getString("phone"));
                landlord1.setNumberid(resultSet.getString("numberid"));
                landlord1.setLoginname(resultSet.getString("loginname"));
                landlord1.setPassword(resultSet.getString("password"));

                list.add(landlord1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public List<User> queryHaveRent(int MasterID, int pageNum, int pageSize) {

        int start = (pageNum - 1) * pageSize;
        String str="SELECT user.* FROM user , `order`  where 1=1 and " +
                "`order`.userid=`user`.userid  AND `order`.masterid= "+MasterID;
        List<User> list=new ArrayList <>();
        User user=new User();
        if (user.getName()!=null){
            str+=" and name like concat('%','"+user.getName()+"','%')";
        }

        if (user.getSex()!=null){
            str+=" and sex like concat('%','"+user.getSex()+"','%')";
        }

        if (user.getPhone()!=null){
            str+=" and phone like concat('%','"+user.getPhone()+"','%')";
        }

        if (user.getNumberid()!=null){
            str+=" and numberid like concat('%','"+user.getNumberid()+"','%')";
        }

        if (user.getLoginname()!=null){
            str+=" and loginname like concat('%','"+user.getLoginname()+"','%')";
        }

        if (user.getPassword()!=null){
            str+=" and password like concat('%','"+user.getPassword()+"','%')";
        }

        str += " limit " + start + "," + pageSize;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                User landlord1=new User();
                landlord1.setId(resultSet.getInt("userid"));
                landlord1.setName(resultSet.getString("name"));
                landlord1.setSex(resultSet.getString("sex"));
                landlord1.setPhone(resultSet.getString("phone"));
                landlord1.setNumberid(resultSet.getString("numberid"));
                landlord1.setLoginname(resultSet.getString("loginname"));
                landlord1.setPassword(resultSet.getString("password"));

                list.add(landlord1);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;

    }

    public int queryCount(User user) {


        String str = "SELECT count(1) countNum FROM user where 1=1 ";
        if (user.getName()!=null){
            str+=" and name like concat('%','"+user.getName()+"','%')";
        }

        if (user.getSex()!=null){
            str+=" and sex like concat('%','"+user.getSex()+"','%')";
        }

        if (user.getPhone()!=null){
            str+=" and phone like concat('%','"+user.getPhone()+"','%')";
        }

        if (user.getNumberid()!=null){
            str+=" and numberid like concat('%','"+user.getNumberid()+"','%')";
        }

        if (user.getLoginname()!=null){
            str+=" and loginname like concat('%','"+user.getLoginname()+"','%')";
        }

        if (user.getPassword()!=null){
            str+=" and password like concat('%','"+user.getPassword()+"','%')";
        }


        Connection connection = ConnDb.getConnection();
        Statement statement = ConnDb.getStatement(connection);
        ResultSet resultSet = null;

        int count=0;
        try {
            resultSet = statement.executeQuery(str);
            while (resultSet.next()) {
                count=resultSet.getInt("countNum");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return count;
    }
}
